"""
设备管理功能数据库迁移
添加场馆设备、门禁码和设备操作日志表
"""

from sqlalchemy import text
from app import db

def upgrade():
    """升级数据库"""
    
    # 创建场馆设备表
    db.session.execute(text("""
        CREATE TABLE IF NOT EXISTS venue_devices (
            id INTEGER NOT NULL AUTO_INCREMENT,
            venue_id INTEGER NOT NULL,
            device_type VARCHAR(50) NOT NULL COMMENT '设备类型: door_lock(门锁), light(灯光), camera(摄像头), air_condition(空调)',
            device_name VARCHAR(100) NOT NULL COMMENT '设备名称',
            device_code VARCHAR(50) NOT NULL UNIQUE COMMENT '设备编号',
            device_model VARCHAR(100) COMMENT '设备型号',
            control_method VARCHAR(50) NOT NULL COMMENT '控制方式: api(接口), qrcode(二维码), manual(手动)',
            api_endpoint VARCHAR(255) COMMENT 'API控制接口地址',
            api_key VARCHAR(255) COMMENT 'API密钥',
            has_access_control BOOLEAN DEFAULT FALSE COMMENT '是否有门禁系统',
            access_control_type VARCHAR(50) COMMENT '门禁类型: self(自主门禁), third_party(第三方门禁)',
            qr_code_duration INTEGER DEFAULT 300 COMMENT '二维码有效期(秒)',
            status SMALLINT DEFAULT 1 COMMENT '设备状态: 0禁用 1启用',
            is_online BOOLEAN DEFAULT TRUE COMMENT '设备是否在线',
            last_heartbeat DATETIME COMMENT '最后心跳时间',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
            PRIMARY KEY (id),
            INDEX idx_venue_id (venue_id),
            INDEX idx_device_type (device_type),
            INDEX idx_device_code (device_code),
            FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """))
    
    # 创建设备门禁码表
    db.session.execute(text("""
        CREATE TABLE IF NOT EXISTS device_access_codes (
            id INTEGER NOT NULL AUTO_INCREMENT,
            device_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            order_id INTEGER COMMENT '关联订单ID',
            activity_id INTEGER COMMENT '关联活动ID',
            access_code VARCHAR(100) NOT NULL UNIQUE COMMENT '门禁码',
            qr_code_data TEXT COMMENT '二维码数据',
            code_type VARCHAR(20) DEFAULT 'temporary' COMMENT '码类型: temporary(临时), permanent(永久)',
            valid_from DATETIME NOT NULL COMMENT '有效开始时间',
            valid_until DATETIME NOT NULL COMMENT '有效结束时间',
            max_uses INTEGER DEFAULT 1 COMMENT '最大使用次数',
            used_count INTEGER DEFAULT 0 COMMENT '已使用次数',
            status SMALLINT DEFAULT 1 COMMENT '状态: 0无效 1有效 2已过期 3已用完',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            used_at DATETIME COMMENT '最后使用时间',
            PRIMARY KEY (id),
            INDEX idx_device_id (device_id),
            INDEX idx_user_id (user_id),
            INDEX idx_order_id (order_id),
            INDEX idx_activity_id (activity_id),
            INDEX idx_access_code (access_code),
            INDEX idx_valid_time (valid_from, valid_until),
            FOREIGN KEY (device_id) REFERENCES venue_devices(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """))
    
    # 创建设备操作日志表
    db.session.execute(text("""
        CREATE TABLE IF NOT EXISTS device_operation_logs (
            id INTEGER NOT NULL AUTO_INCREMENT,
            device_id INTEGER NOT NULL,
            user_id INTEGER,
            access_code_id INTEGER,
            operation_type VARCHAR(50) NOT NULL COMMENT '操作类型: open_door(开门), close_door(关门), turn_on_light(开灯), turn_off_light(关灯)',
            operation_method VARCHAR(50) COMMENT '操作方式: qrcode(二维码), api(接口), manual(手动)',
            operation_result VARCHAR(20) DEFAULT 'success' COMMENT '操作结果: success(成功), failed(失败)',
            error_message TEXT COMMENT '错误信息',
            ip_address VARCHAR(45) COMMENT '操作者IP地址',
            user_agent VARCHAR(255) COMMENT '用户代理',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
            PRIMARY KEY (id),
            INDEX idx_device_id (device_id),
            INDEX idx_user_id (user_id),
            INDEX idx_access_code_id (access_code_id),
            INDEX idx_operation_type (operation_type),
            INDEX idx_created_at (created_at),
            FOREIGN KEY (device_id) REFERENCES venue_devices(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY (access_code_id) REFERENCES device_access_codes(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """))
    
    db.session.commit()
    print("✅ 设备管理功能表创建成功")

def downgrade():
    """降级数据库"""
    db.session.execute(text("DROP TABLE IF EXISTS device_operation_logs;"))
    db.session.execute(text("DROP TABLE IF EXISTS device_access_codes;"))
    db.session.execute(text("DROP TABLE IF EXISTS venue_devices;"))
    db.session.commit()
    print("✅ 设备管理功能表删除成功")

if __name__ == '__main__':
    upgrade() 